import re

from apscheduler import util
from apscheduler.schedulers.background import BackgroundScheduler
from django.core import serializers
from django.http.response import JsonResponse
from django.http.response import HttpResponseBadRequest
from django.views.decorators.http import require_http_methods
import pandas as pd
from crontab import CronTab
import json
import backend.views as backv

from django_apscheduler.jobstores import DjangoJobStore

import utils.custom_utils as utils
import utils.http_response as hr
from backend import schedule_jobs
from backend.models import HbdMenu, Aa10
from mysite import db_config

job_reg = re.compile('invoke_job[0-9]')


def encrypy_password(connection_string):
    """将连接串中的密码替换为*号
    """
    str1 = connection_string.split('@')[0].split(':')[0]
    str2 = connection_string.split('@')[0].split(':')[1]
    str3 = connection_string.split('@')[1]
    return f'{str1}:{str2}:******@{str3}'


@require_http_methods(['GET'])
def db_query(request):
    try:
        conn = db_config.sqlalchemy_conn()
        db = pd.read_sql(
            "select name,db_type,connection_string,db,ip,note,id from source_db_info where effective='1'  order by name,db_type",
            con=conn)

        db['connection_string'] = db['connection_string'].apply(encrypy_password)

        data = {
            'db_type': db['db_type'].values.tolist(),
            'name': db['name'].values.tolist(),
            'connection_string': db['connection_string'].values.tolist(),
            'db': db['db'].values.tolist(),
            'ip': db['ip'].values.tolist(),
            'note': db['note'].values.tolist(),
            'rowid': db['id'].values.tolist()
        }
        return JsonResponse({'data': data, 'code': 1000})
    except Exception as e:
        return HttpResponseBadRequest(content=e)
    finally:
        conn.dispose()


@require_http_methods(['POST'])
def db_update(request):
    db_id = request.POST.get('id')
    ip = request.POST.get('ip')
    name = request.POST.get('name')
    user = request.POST.get('user')
    password = request.POST.get('password')
    db = request.POST.get('db')
    port = request.POST.get('port')
    db_type = request.POST.get('db_type')
    charset = request.POST.get('charset')
    note = request.POST.get('note')
    effective = request.POST.get('effective')

    if db_type == 'mysql':
        connection_string = f'mysql+mysqldb://{user}:{password}@{ip}:{port}/{db}?charset={charset}'
    elif db_type == 'oracle':
        connection_string = f'oracle://{user}:{password}@{ip}:{port}/?service_name={db}'
    elif db_type == 'sqlserver':
        connection_string = f'mssql+pymssql://{user}:{password}@{ip}:{port}/{db}?charset={charset}'
    elif db_type == 'postgresql':
        connection_string = f'postgresql://{user}:{password}@{ip}:{port}/{db}'

    try:
        conn = db_config.mysql_connect()
        with conn.cursor() as curs:
            sql = f"""update source_db_info
                        set name='{name}',
                        user = '{user}',
                        connection_string='{connection_string}',
                        ip='{ip}',
                        passwd='{password}',
                        db='{db}',
                        port={port},
                        db_type='{db_type}',
                        effective='1',
                        note='{note}'
                        where id={db_id}"""
            curs.execute(sql)
        conn.commit()
        return JsonResponse({'data': '修改成功', 'code': 1000})
    except Exception as e:
        conn.rollback()
        return HttpResponseBadRequest(content=e)
    finally:
        conn.close()


@require_http_methods(['POST'])
def db_insert(request):
    name = request.POST.get('name')
    ip = request.POST.get('ip')
    user = request.POST.get('user')
    password = request.POST.get('password')
    db = request.POST.get('db')
    port = request.POST.get('port')
    db_type = request.POST.get('db_type')
    charset = request.POST.get('charset')
    note = request.POST.get('note')
    catalog = request.POST.get('catalog')
    schema = request.POST.get('schema')

    if db_type == 'mysql':
        connection_string = f'mysql+mysqldb://{user}:{password}@{ip}:{port}/{db}?charset={charset}'
    elif db_type == 'oracle':
        connection_string = f'oracle://{user}:{password}@{ip}:{port}/?service_name={db}'
    elif db_type == 'sqlserver':
        connection_string = f'mssql+pymssql://{user}:{password}@{ip}:{port}/{db}?charset={charset}'
    elif db_type == 'postgresql':
        connection_string = f'postgresql://{user}:{password}@{ip}:{port}/{db}'
    elif db_type == 'presto':
        """ 以 parquet 格式读取"""
        if catalog:
            connection_string = f'presto://{ip}:{port}/{catalog}/parquet'
        else:
            return HttpResponseBadRequest("presto  数据源 catalog(hive | mysql) 不能为空")
    elif db_type == 'hive':
        if catalog is None:
            catalog = 'default'
        connection_string = f'hive://{ip}:{port}/{catalog}'
    try:
        conn = db_config.mysql_connect()
        with conn.cursor() as curs:
            sql = f"""insert into source_db_info(name,connection_string,ip,user,passwd,db,port,db_type,note)
                        values('{name}','{connection_string}','{ip}','{user}','{password}','{db}',{port},'{db_type}','{note}')"""
            curs.execute(sql)
        conn.commit()
        return JsonResponse({'data': '新增成功', 'code': 1000})
    except Exception as e:
        conn.rollback()
        return HttpResponseBadRequest(content=e)
    finally:
        conn.close()


@require_http_methods(['POST'])
def crontab_enable(request):
    """
    启用/禁用自动检核的crontab任务
    :param request:
    :return:
    """
    enable = request.POST.get('enable')
    job_name = request.POST.get('job_name')

    cron = CronTab(user=True)
    job = list(cron.find_comment(job_name))[0]

    if enable == 'false':
        # job.enable(False)
        # cron.write()
        return JsonResponse({"msg": "success"})
    elif enable == 'true':
        # job.enable()
        # cron.write()
        return JsonResponse({"msg": "success"})
    else:
        return JsonResponse({"msg": "failed"})


@require_http_methods(['POST'])
def crontab_run(request):
    job_name = request.POST.get('job_name')

    try:
        cron = CronTab(user=True)
        job = list(cron.find_comment(job_name))[0]
        job.run()
        return JsonResponse({"msg": "success"})
    except Exception as e:
        return HttpResponseBadRequest(content=e)


@require_http_methods(['POST'])
def menu_add(request):
    return_msg = hr.repo().success()
    try:
        body_info = request.body
        params = json.loads(body_info)
        menu = HbdMenu(menu_name=params.get('menu_name'),
                       menu_name_en=params.get('menu_name_en'),
                       menu_url_dashboard=params.get('menu_url_dashboard'),
                       menu_url_detail=params.get('menu_url_detail'),
                       menu_url_rule=params.get('menu_url_rule'),
                       menu_validity=params.get('menu_validity'),
                       menu_auto_run=params.get('menu_auto_run'))
        menu.save()
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")
    return JsonResponse(return_msg)


@require_http_methods(['POST'])
def menu_delete(request):
    return_msg = hr.repo().success()
    try:
        body_info = request.body
        params = json.loads(body_info)
        hbd_menu = HbdMenu.objects.get(id=int(params.get("id")))
        hbd_menu.delete()
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")
    return JsonResponse(return_msg)


@require_http_methods(['POST'])
def menu_update(request):
    return_msg = hr.repo().success()
    try:
        body_info = request.body
        params = json.loads(body_info)
        hbd_menu = HbdMenu.objects.get(id=int(params.get("id")))
        hbd_menu.menu_name = params.get('menu_name')
        hbd_menu.menu_name_en = params.get('menu_name_en')
        hbd_menu.menu_url_dashboard = params.get('menu_url_dashboard')
        hbd_menu.menu_url_detail = params.get('menu_url_detail')
        hbd_menu.menu_url_rule = params.get('menu_url_rule')
        hbd_menu.menu_validity = params.get('menu_validity')
        hbd_menu.menu_auto_run = params.get('menu_auto_run')
        hbd_menu.save()
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")
    return JsonResponse(return_msg)


@require_http_methods(['POST'])
def menu_query(request):
    return_msg = hr.repo().success()
    params = {}
    try:
        body_info = request.body
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
        hbd_menus = HbdMenu.objects.all().filter(**params)
        menus_arr = []
        for menu in hbd_menus:
            menu_dict = utils.to_dict(menu)
            menus_arr.append(menu_dict)
        return_msg.update({"data": menus_arr})
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")
    return JsonResponse(return_msg)


scheduler = BackgroundScheduler()  # 创建一个调度器对象
store = DjangoJobStore()
scheduler.add_jobstore(store, "default")  # 添加一个作业


@require_http_methods(['POST'])
def scheduler_add(request):
    """
    添加定时任务
    :param request:
    :return:
    """
    # {"id": "job_test", "name": "测试", "func": "job1", "replace": 1 ,  "trigger": {"day": "*", "second": "*/5"}}
    return_msg = hr.repo().success()
    params = {}
    body_info = request.body
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
        # cron 表达式
        trigger = params.get('trigger')
        if trigger is None:
            raise Exception(hr.repo().fail("trigger 不能为空"))
        # 需要执行的方法 只能将方法放在schedule_jobs 中
        exec_func = params.get('func')
        # 是否存在时替换
        replace = bool(params.get('replace')) if params.get('replace') else True
        job_id = params.get('id')
        job_name = params.get('name')
        # 将该值设置为 none时  暂停
        next_run_job = params.get('next_run_job')
        # next_run_job = util.undefined if next_run_job is None else next_run_job
        # 获取schedule_jobs 中的exec_func 对应的方法
        job_function = getattr(schedule_jobs, exec_func)
        if not job_function:
            raise Exception("schedule_jobs 中不存在 【 " + str(exec_func) + " 】")
        kw_dict = {"params": {"company": job_id}} if job_reg.search(exec_func) else None
        backv.scheduler.add_job(job_function, 'cron', id=job_id, name=job_name, replace_existing=replace,
                                kwargs=kw_dict, **trigger)
        # 不添加该行  不会保存至数据库中
        # backv.scheduler.start()
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")

    return JsonResponse(return_msg)


@require_http_methods(['POST'])
def scheduler_replace(request):
    """
    替换定时任务
    :param request:
    :return:
    """
    # {"id": "job_test", "name": "测试", "func": "job1", "replace": 1 ,  "trigger": {"day": "*", "second": "*/5"}}
    return_msg = hr.repo().success()
    params = {}
    body_info = request.body
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
        # cron 表达式
        trigger = params.get('trigger')
        if trigger is None:
            raise Exception(hr.repo().fail("trigger 不能为空"))
        # 需要执行的方法 只能将方法放在schedule_jobs 中
        exec_func = params.get('func')
        # 是否存在时替换
        replace = True
        job_id = params.get('id')
        job_name = params.get('name')
        # 将该值设置为 none时  暂停
        # next_run_job = params.get('next_run_job')
        # next_run_job = util.undefined if next_run_job is None else next_run_job
        # 获取schedule_jobs 中的exec_func 对应的方法
        job_function = getattr(schedule_jobs, exec_func)
        kw_dict = {"params": {"company": job_id}} if job_reg.search(exec_func) else None
        # backv.scheduler.add_job(job_function, 'cron', id=job_id, name=job_name, replace_existing=replace, **trigger)
        backv.scheduler.add_job(job_function, 'cron', id=job_id, name=job_name, replace_existing=replace,
                                kwargs=kw_dict, **trigger)
        # 不添加该行  不会保存至数据库中
        backv.scheduler.start()
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")

    return JsonResponse(return_msg)


@require_http_methods(['POST'])
def scheduler_stop(request):
    """
    添加定时任务
    :param request:
    :return:
    """
    # {"id": "job_test", "name": "测试", "func": "job1", "replace": 1 , "next_run_time":None, "trigger": {"day": "*", "second": "*/5"}}
    return_msg = hr.repo().success()
    params = {}
    body_info = request.body
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
        job_id = params.get('id')
        # scheduler.pause_job(job_id=job_id)
        backv.store.lookup_job(job_id=job_id).pause()
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")

    return JsonResponse(return_msg)


@require_http_methods(['POST'])
def scheduler_remove(request):
    """
    添加定时任务
    :param request:
    :return:
    """
    # {"id": "job_test", "name": "测试", "func": "job1", "replace": 1 , "next_run_time":None, "trigger": {"day": "*", "second": "*/5"}}
    return_msg = hr.repo().success()
    params = {}
    body_info = request.body
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
        job_id = params.get('id')
        # scheduler.remove_job(job_id=job_id)
        backv.store.remove_job(job_id=job_id)
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")

    return JsonResponse(return_msg)


@require_http_methods(['POST'])
def scheduler_start(request):
    """
    添加定时任务
    :param request:
    :return:
    """
    # {"id": "job_test", "name": "测试", "func": "job1", "replace": 1 , "next_run_time":None, "trigger": {"day": "*", "second": "*/5"}}
    return_msg = hr.repo().success()
    params = {}
    body_info = request.body
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
        job_id = params.get('id')
        # scheduler.resume_job(job_id=job_id)
        backv.store.lookup_job(job_id=job_id).resume()
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")

    return JsonResponse(return_msg)


@require_http_methods(['POST'])
def aa10_query(request):
    return_msg = hr.repo().success()
    params = {}
    try:
        body_info = request.body
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
        aa10s = Aa10.objects.all().filter(**params)
        aa10_arr = []
        for aa10 in aa10s:
            aa10_dict = utils.to_dict(aa10)
            aa10_arr.append(aa10_dict)
        return_msg.update({"data": aa10_arr})
    except Exception as e:
        return_msg = hr.repo().fail(f"{e}")
    return JsonResponse(return_msg)
